package com.ar4j.sql;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Map;

/**
 * Utility class for composing SQL statements
 */

public class SqlUtils {
  
  /**
   * No construction allowed...EVER!!!
   */
  private SqlUtils() {
    // UTIL CLASS - NO CONSTRUCTOR FOR YOU!!!
  }

  /**
   * Converts a field (property) name to a column name. CamelCase is converted to underscore form.
   * 
   * Ripped off from Spring's BeanPropertyRowMapper class (underscoreName method).
   * 
   */
  public static String convertPropertyNameToUnderscoreName(String propertyName) {
    StringBuilder result = new StringBuilder();
    if (propertyName != null && propertyName.length() > 0) {
      result.append(propertyName.substring(0, 1).toLowerCase());
      for (int i = 1; i < propertyName.length(); i++) {
        String s = propertyName.substring(i, i + 1);
        if (s.equals(s.toUpperCase())) {
          result.append("_");
          result.append(s.toLowerCase());
        }
        else {
          result.append(s);
        }
      }
    }
    return result.toString();
  }
  
  /**
   * Appends all column names to the given string builder, separating them by commas and prefixing them
   * by the given table prefix.
   */
  public static void appendCommaDelimitedColumnList(StringBuilder sql, String tablePrefix, Collection<String> columns) {
    boolean first = true;
    for(String column : columns) {
      if(!first) {
        sql.append(", ");
      } else {
        first = false;
      }
      
      if(tablePrefix != null) {
        sql.append(tablePrefix);
        sql.append(".");
      }
      
      sql.append(column);
    }
  }
  
  /**
   * Composes and appends a SQL WHERE clause to the given string builder. Fields that are not collections are compared 
   * directory (equals). Fields that are collections are compared using the IN operator. Field names are mapped to column
   * names using the given fieldToColumnMap.
   */
  public static void appendWhereClause(StringBuilder sql, Collection<String> fields, Collection<String> collectionFields, String tablePrefix, Map<String, String> fieldToColumnMap) {
    sql.append(" WHERE ");
    boolean first = true;
    for(String field : fields) {
      if(!first) {
        sql.append(" AND ");
      } else {
        first = false;
      }
      
      String columnName = fieldToColumnMap.get(field);
      if(columnName == null) {
        throw new IllegalArgumentException("No column name for found: " + field);
      }
      
      String operatorPrefix = " = :";
      String operatorSuffix = "";
      if(collectionFields.contains(field)) {
        operatorPrefix = " IN (:";
        operatorSuffix = ")";
      }
      
      if(tablePrefix != null) {
        sql.append(tablePrefix);
        sql.append(".");
      }
      
      sql.append(columnName);
      sql.append(operatorPrefix);
      sql.append(field);
      sql.append(operatorSuffix);
    }
  }
  
  /**
   * Extracts a value of the required type from the given result set at the given column index.
   * This uses the various getXXXX methods in the result set as opposed to using getObject for
   * known types (i.e. long, int, String). The getObject fallback is used for any type that is
   * not recognized.
   */
  @SuppressWarnings("unchecked")
  public static Object extractValueFromResultSet(ResultSet rs, int index, Class<?> type) 
    throws SQLException {
   
    Object value = null;
    
    // extract by type for known types, or as an object if we are not sure
    if(Boolean.class == type || boolean.class == type) {
      value = rs.getBoolean(index);
    } else if (Byte.class == type || byte.class == type) {
      value = rs.getByte(index);
    } else if (Short.class == type || short.class == type) {
      value = rs.getShort(index);
    } else if (Integer.class == type || int.class == type) {
      value = rs.getInt(index);
    } else if (Long.class == type || long.class == type) {
      value = rs.getLong(index);
    } else if (Float.class == type || float.class == type) {
      value = rs.getFloat(index);
    } else if (Double.class == type || double.class == type) {
      value = rs.getDouble(index);
    } else if (String.class == type) {
      value = rs.getString(index);
    } else if (Date.class.isAssignableFrom(type)) {
      Timestamp ts = rs.getTimestamp(index);
      if(!rs.wasNull()) {
        value = new Date(ts.getTime());
      }
    } else if (type.isEnum()) {
      String enumValueName = rs.getString(index);
      if(!rs.wasNull()) {
        value = Enum.valueOf((Class<? extends Enum>) type, enumValueName);
      }
    } else {
      value = rs.getObject(index);
    }
    
    // final check to make sure the last value was not null
    if(rs.wasNull()) {
      value = null;
    }
   
    return value;
  }
  
}
